/* 

Paper: Gentrification and pioneer businesses 
Authors: Behrens, Boulam, Martin, Mayneris 

Name dofile: create_db_concordedblocks_philly.do  
Version: 16 nov. 2021 

Output: id_conc_pop_income_philly.dta 

Inputs:

- gentri_dum_id_conc_philly.dta (from identify_gentri_blocks_philly.do)
- id_conc_disk_500m_philly.dta (GIS, publicly available)
- id_conc_database_philly.dta (from create_db_concordedblocks_philly.do)
*/ 

************************************
*Creation of the pop_income database
************************************

*********
*Ring 500
*********

use gentri_dum_id_conc_philly.dta, clear

rename id_conc id_conc_disc500

saveold temp, replace v(13)

use id_conc_disk_500m_philly.dta, clear 
merge m:1 id_conc_disc500 using temp
keep if _m==3 
drop _m

bys id_conc: egen gentri_9000_500=max(gentri1990)
bys id_conc: egen gentri_0010_500=max(gentri2000)
duplicates drop id_conc, force

keep id_conc gentri_9000_500 gentri_0010_500
saveold gentri_500, v(13) replace


use id_conc_database_philly, clear
keep id_conc t pop housing edu_college_block pop_black_block pop_asian_block pop_white_block pop_other_block median_yearb_block median_crent_block median_grent_block median_value_block percap_income_block
rename id_conc id_conc_disc500 


joinby id_conc_disc500 using id_conc_disk_500m_philly.dta

foreach v of var median_yearb_block median_crent_block median_grent_block median_value_block  {
bys id_conc t: egen housing_tot=sum(housing) if (`v'>0&`v'!=.)
gen sh=housing/housing_tot
replace `v'=sh*`v'
drop sh housing_tot
}

foreach v of var percap_income_block {
bys id_conc t: egen pop_tot=sum(pop) if `v'!=.
gen sh=pop/pop_tot
replace `v'=sh*`v'
drop sh pop_tot
}

collapse (sum) pop edu_college_block pop_black_block pop_asian_block pop_white_block pop_other_block percap_income_block housing median_yearb_block median_crent_block median_grent_block median_value_block, by(id_conc t)

rename edu_college_block edu 
rename pop_black_block black
rename pop_asian_block asian
rename pop_white_block white
rename pop_other_block other
rename percap_income_block pc_income
rename median_value_block housing_value
rename median_crent_block crent
rename median_grent_block grent
rename median_yearb med_yearb

foreach v of var pop edu black asian white other pc_income med_yearb housing crent grent housing_value{
rename `v' `v'500
}

*We merge gentri information at the neighborhood and id_conc level
merge m:1 id_conc using gentri_500
drop _m

gen year=1990
replace year=2000 if t==2
replace year=2010 if t==3

gen gentri_init500= ((gentri_9000_500==1&year==1990)|(gentri_0010_500==1&year==2000))
label var gentri_init500 "Gentrifying id_conc in 500iguous id_conc, beginning of the period"
gen gentri_end500= ((gentri_9000_500==1&year==2000)|(gentri_0010_500==1&year==2010))
label var gentri_end500 "Gentrifying id_conc in contiguous id_conc, end of the period"

saveold temp3, replace v(13)

use id_conc_database_philly, clear

rename edu_college_block edu 
rename pop_black_block black
rename pop_asian_block asian
rename pop_white_block white
rename pop_other_block other
rename percap_income_block pc_income
rename median_value_block housing_value
rename median_crent_block crent
rename median_grent_block grent
rename median_yearb med_yearb


keep id_conc t dis_ws pop edu black asian white other pc_income housing med_yearb crent grent housing_value
merge 1:1 id_conc t using temp3
keep if _m==3
drop _m

foreach v of var pop* edu* black* asian* white* other* pc_income* housing* med_yearb* crent* grent* {
g l`v' =log(`v') 
}

tsset id_conc t 

keep if dis_ws<=30

tab t, gen(time)


saveold id_conc_pop_income_philly, replace v(13)


erase "temp.dta"
erase "temp3.dta"
erase "gentri_500.dta"



